package cn.wizzer.app.web.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.nutz.dao.Dao;
/**
 * 功能说明: 导入Excel
 * <p/>
 * 创建说明: 2016年07月29日 下午1:56:09 虞秀斌
 * <p/>
 * 修改历史:
 */
public class DoExcelUtil<T> {

	private T entity;

	public DoExcelUtil(T entity) {
		this.entity = entity;
	}
	/**
     * 反射机制将数据塞入对象
     *
     * @param methodName 列名
     * @param obj 对象
     * @return list<对象>  ，对象的集合。
     */
	public boolean doInitEntity(String methodName, Object obj) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		methodName = "set" + methodName.substring(0, 1).toUpperCase() + methodName.substring(1);
		Method[] methods = entity.getClass().getMethods();
		for (Method method : methods) {
			if (methodName.equals(method.getName())) {
				Class<?>[] clazz = method.getParameterTypes();
				String type = clazz[0].getName();
				if (type.equals("java.lang.String")) {
					method.invoke(entity, (String) obj);
					return true;
				} else if (type.equals("java.util.Date")) {
					method.invoke(entity, (Date) obj);
					return true;
				} else if (type.equals("java.lang.Integer")) {
					method.invoke(entity, new Integer((Integer) obj));
					return true;
				} else {
					return false;
				}
			}
		}
		return false;
	}
	/**
     * 导入数据公用方法
     *
     * @param execelFile excel的服务器路径
     * @param heads 导入的信息项（列名，小写字母，按excel表头顺序排列）
     * @param headNum 获取数据开始列  ，从0 开始，列如数据在第2行开始，这里填1.
     * @return list<对象>  ，对象的集合。
     */
	public List<T> excel( String execelFile, String[] heads,int headNum)throws Exception {
		List<T> Tlist = new ArrayList<T>();
			// Excel 2003
			Workbook book = new HSSFWorkbook(new FileInputStream(execelFile));
			Sheet sheet = book.getSheetAt(0);
			Row row;
			String cell;
			int totalRows = sheet.getLastRowNum();
			for (int i = headNum; i <= totalRows; i++) {
				row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				//int totalCells = row.getLastCellNum();
				List list = new ArrayList();
				for (int j = row.getFirstCellNum(); j < heads.length; j++) {
					if (row.getCell(j) == null) {
						list.add(j, "");
						continue;
					}
					if (0 == row.getCell(j).getCellType().getCode()) {
						if (HSSFDateUtil.isCellDateFormatted(row.getCell(j))) {
							Date d = row.getCell(j).getDateCellValue();
							DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
							cell = formater.format(d);
						} else {
							DecimalFormat df = new DecimalFormat("########");
							cell = df.format(row.getCell(j).getNumericCellValue());
						}
					} else {
						cell = row.getCell(j).toString();
					}
					if (StringUtils.isNotBlank(cell)) {
						cell = cell.trim();
					}
					// System.out.print(cell+"  ");
					list.add(j, cell);
				}
				/*System.out.println();*/
				int a = 0;
				for (Object object : list) {
					doInitEntity(heads[a], object);
					a++;
				}
				Tlist.add(i - headNum, entity);
			}
		return Tlist;
	}


	/**
	 * 读取后缀为“xlsx”的excel文件代码
	 * @param filePath       需要读取的Excel文件
	 * @param sheetIndex 读取的Excel文件中的表格下标,第几个sheet,从0开始
	 * @param headNum 第几行开始，从0开始
	 * @param lengthRow 数据列的长度，从0开始
	 * @return 数据的坐标，对应的值
	 */
	public static ArrayList<ArrayList<Object>> readExcel(String filePath, Integer sheetIndex,Integer headNum,Integer lengthRow) {
		File file = new File(filePath);
		ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
		FileInputStream fileInputStream = null;
		try {
			ArrayList<Object> colList;
			fileInputStream = new FileInputStream(file);
			XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
			XSSFSheet sheet = wb.getSheetAt(sheetIndex);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			int rowsCount = sheet.getPhysicalNumberOfRows();
			for (int i = headNum;i < rowsCount; i++ ) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				//当读取行为空时，跳过
				if (row == null) {
					continue;
				}
				//for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				for (int j = 0; j <= lengthRow; j++) {
					if (row.getFirstCellNum() < 0) {
						continue;
					}
					cell = row.getCell(j);
					//当该单元格为空
					//if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
					if (cell == null || cell.getCellType() == CellType.BLANK) {
						colList.add("");
						continue;
					}
					//根据数据类型来获取值
					switch (cell.getCellType()) {
						case STRING:
							value = cell.getStringCellValue().trim();
							break;
						case NUMERIC:
							if (cell.getCellStyle().getDataFormatString().contains("m/d/yy")) {
								value = DateUtil.format(cell.getDateCellValue(),"yyyy-MM-dd");
							} else if (cell.getCellStyle().getDataFormatString().contains("yyyy/m/d")) {
								value = DateUtil.format(cell.getDateCellValue(),"yyyy-MM-dd");
							} else if (cell.getCellStyle().getDataFormatString().equals("General")) {
								value = getRealStringValueOfDouble(cell.getNumericCellValue());
							} else {
								try {
									value = getRealStringValueOfDouble(cell.getNumericCellValue());
								}catch (Exception e){
									value=cell.toString().trim();
								}
							}
							break;
						case BOOLEAN:
							value = Boolean.valueOf(cell.getBooleanCellValue());
							break;
						case BLANK:
							value = "";
							break;
						case FORMULA: //公式类型
							value = parseFormula(cell);
							break;
						default:
							value = cell.toString().trim();
					}
					colList.add(value);
				}
				rowList.add(colList);
			}
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (null != fileInputStream) {
					fileInputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return rowList;
	}


	/**
	 * 解析公式
	 * @param cell - 单元格
	 * @return String - 结果
	 */
	public static String parseFormula(Cell cell) {
		String data = null;
		switch (cell.getCachedFormulaResultType()) {
			case NUMERIC:
				if (0 == cell.getCellStyle().getDataFormat()) {
					data = String.format("%.4f", cell.getNumericCellValue());
				} else {
					data = String.valueOf(cell.getNumericCellValue());
				}
				break;
			case STRING:
				data = String.valueOf(cell.getRichStringCellValue());
				break;
			case BOOLEAN:
				data = String.valueOf(cell.getBooleanCellValue());
				break;
			case ERROR:
				data = String.valueOf(cell.getErrorCellValue());
				break;
			default:
				data = cell.getCellFormula();
		}
		return data;
	}

	private static String getRealStringValueOfDouble(Double d) {
		String doubleStr = d.toString();
		boolean b = doubleStr.contains("E");
		int indexOfPoint = doubleStr.indexOf('.');
		if (b) {
			int indexOfE = doubleStr.indexOf('E');
			BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
					+ BigInteger.ONE.intValue(), indexOfE));
			int pow = Integer.valueOf(doubleStr.substring(indexOfE
					+ BigInteger.ONE.intValue()));
			int xsLen = xs.toByteArray().length;
			int scale = xsLen - pow > 0 ? xsLen - pow : 0;
			doubleStr = String.format("%." + scale + "f", d);
		} else {
			Pattern p = Pattern.compile(".0$");
			java.util.regex.Matcher m = p.matcher(doubleStr);
			if (m.find()) {
				doubleStr = doubleStr.replace(".0", "");
			}
		}
		return doubleStr;
	}
}
